
# Database requests that cannot be handled by Django ORM.
from django.db import connection

def getUserTags(user_id, published_only):
    """
    Returns a list of tags of current user in form (<tag name>, <number of occurrence in all or published-only messages> ).
    """
    cursor = connection.cursor()

    select_clause = 'select stream_tag.name as name, count(*) as count from stream_message inner join stream_message_tags on stream_message.id = stream_message_tags.message_id inner join stream_tag on stream_tag.name = stream_message_tags.tag_id'
    where_clause = 'where stream_message.user_id = %s and stream_message.ignored = false'
    if published_only:
        where_clause = where_clause + ' and stream_message.published <> false'
    group_clause = 'group by stream_tag.name'
    order_clause = 'order by stream_tag.name'

    query_string = ' '.join([select_clause, where_clause, group_clause, order_clause])
    cursor.execute(query_string, (user_id,))

    return cursor.fetchall()

def getSubscriptionsWithUnread(user_id):
    cursor = connection.cursor()
    query_string = 'select stream_subscription.id as id, stream_subscription.name as name, count(msg.id) as unread from stream_subscription left outer join stream_message_subscriptions on stream_subscription.id = stream_message_subscriptions.subscription_id left outer join (select stream_message.id as id from stream_message where stream_message.read = false and stream_message.ignored = false and stream_message.user_id = %s) as msg on msg.id = stream_message_subscriptions.message_id where stream_subscription.user_id = %s group by stream_subscription.id, stream_subscription.name order by stream_subscription.name'
    cursor.execute(query_string, (user_id, user_id))

    return cursor.fetchall()
